{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we want to work with SQL easily in an Ipython notebook, we'll load the ipython-sql [extension](https://github.com/catherinedevlin/ipython-sql) as follows:\n",
    "\n",
    "**Note: DO NOT PANIC** \n",
    "* Don't worry if you get (a) a big red-highlighted warning or (b) a note that the extension has already been loaded!  As long as your SQL commands work, it's loaded properly!\n",
    "\n",
    "* If you don't understand all the SQL queries... very soon, you will!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we'll load an SQLite database stored as a file as follows:\n",
    "\n",
    "**NOTE: We load a file below (here, \"dataset_0.db\", which must be in the same directory as the notebook. You'll use this file throughout the next few lectures and PS1. Make sure to download the from the webpage!!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @dataset_0.db'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///dataset_0.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets look at one table from the US National Oceanic and Atmospheric Administration (NOAA) Rainfall dataset- `precipitation_full`- having the following schema:\n",
    "\n",
    "> * `state_code`\n",
    "> * `station_id`\n",
    "> * `year`\n",
    "> * `month`\n",
    "> * `day`\n",
    "> * `hour`\n",
    "> * `precipitation`\n",
    "> * `flag_1`\n",
    "> * `flag_2`\n",
    "\n",
    "Each tuple in this table describes one hour of rainfall (`precipitation`- in hundredths of an inch) at one station (`station_id`) in one state (`state_code`).  Note that tuples with `hour=25` record the total rainfall for that day, and that we can ignore the values of attributes `flag_1` and `flag_2` for now.\n",
    "\n",
    "Let's take a look at some sample rows of the table via SQL queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that you can also load an empty in-memory database using:\n",
    "\n",
    "``%sql sqlite://``\n",
    "\n",
    "Now let's try out some queries!  (Don't worry, if you're new to SQL, we'll go over all the syntax in more depth next lecture)\n",
    "\n",
    "**`%sql` is used for single line SQL commands:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///dataset_0.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>state_code</th>\n",
       "        <th>station_id</th>\n",
       "        <th>year</th>\n",
       "        <th>month</th>\n",
       "        <th>day</th>\n",
       "        <th>hour</th>\n",
       "        <th>precipitation</th>\n",
       "        <th>flag_1</th>\n",
       "        <th>flag_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>807</td>\n",
       "        <td>2013</td>\n",
       "        <td>9</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>None</td>\n",
       "        <td>[</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>807</td>\n",
       "        <td>2013</td>\n",
       "        <td>9</td>\n",
       "        <td>1</td>\n",
       "        <td>25</td>\n",
       "        <td>0</td>\n",
       "        <td>I</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 807, 2013, 9, 1, 1, None, '[', ''), (1, 807, 2013, 9, 1, 25, 0, 'I', '')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM precipitation_full LIMIT 2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///dataset_0.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74251</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(74251,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM precipitation_full;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And **`%%sql` is used for multi-line SQL commands:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SUM(p.precipitation)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31982</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(31982,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(p.precipitation) \n",
    "FROM precipitation_full p, states s \n",
    "WHERE p.state_code = s.code AND s.abbrev = 'CA';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also work with the output of the queries we issue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    }
   ],
   "source": [
    "result = %sql SELECT * FROM states;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'code', u'name', u'abbrev']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.keys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note that the first row is the header row with column names!*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, u'Alabama', u'AL')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Alabama'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result[1].name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now feel free to have fun with the dataset- we'll see more of it later though!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
